{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f8361e40",
   "metadata": {},
   "source": [
    "## Storing data in a networked Postgres database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da7a4f71",
   "metadata": {},
   "source": [
    "To avoid the \"pg_config executable not found\" error when installing the psycopg2 package, ensure that the necessary PostgreSQL libraries and headers are installed on your system. For Debian/Ubuntu systems, execute sudo apt-get install libpq-dev python3-dev. For Red Hat/CentOS/Fedora, use sudo yum install postgresql-devel python3-devel. On macOS, install PostgreSQL with Homebrew using brew install postgresql. Alternatively, you can install psycopg2-binary with pip install psycopg2-binary, which includes the required dependencies without needing compilation. However, psycopg2-binary is more suitable for development and limited testing rather than production environments. If issues persist after these steps, try reinstalling SQLAlchemy with pip install --force-reinstall sqlalchemy. Following these steps will enable smooth installation and operation of psycopg2 and SQLAlchemy for connecting to a PostgreSQL database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c395eae",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install sqlalchemy psycopg2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "117eb924",
   "metadata": {},
   "outputs": [],
   "source": [
    "import exchange_calendars as xcals\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown, display\n",
    "from openbb import obb\n",
    "from sqlalchemy import create_engine, text\n",
    "from sqlalchemy.exc import ProgrammingError"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d01dd60f",
   "metadata": {},
   "outputs": [],
   "source": [
    "obb.user.preferences.output_type = \"dataframe\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6d20df8",
   "metadata": {},
   "source": [
    "Database connection parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdcb5964",
   "metadata": {},
   "outputs": [],
   "source": [
    "username = \"\"\n",
    "password = \"\"\n",
    "host = \"127.0.0.1\"\n",
    "port = \"5432\"\n",
    "database = \"market_data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c72e535c",
   "metadata": {},
   "outputs": [],
   "source": [
    "DATABASE_URL = f\"postgresql://{username}:{password}@{host}:{port}/postgres\"\n",
    "base_engine = create_engine(DATABASE_URL)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68b1ddca",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to create a new database and return an engine for that database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "721578aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_database_and_get_engine(db_name, base_engine):\n",
    "    conn = base_engine.connect()\n",
    "    conn = conn.execution_options(isolation_level=\"AUTOCOMMIT\")\n",
    "\n",
    "    try:\n",
    "        conn.execute(text(f\"CREATE DATABASE {db_name};\"))\n",
    "    except ProgrammingError:\n",
    "        pass\n",
    "    finally:\n",
    "        conn.close()\n",
    "\n",
    "    conn_str = base_engine.url.set(database=db_name)\n",
    "\n",
    "    return create_engine(conn_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3960ff6e",
   "metadata": {},
   "source": [
    "Create the database and get an engine for it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba97d273",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_database_and_get_engine(\"stock_data\", base_engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4fb8b7d",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to fetch historical stock data for a given symbol and date range, and add a 'symbol' column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90c1ff04",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "def get_stock_data(symbol, start_date=None, end_date=None):\n",
    "    data = obb.equity.price.historical(\n",
    "        symbol,\n",
    "        start_date=start_date,\n",
    "        end_date=end_date,\n",
    "        provider=\"yfinance\",\n",
    "    )\n",
    "    data.reset_index(inplace=True)\n",
    "    data[\"symbol\"] = symbol\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1aa9fc39",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to save the fetched stock data to a PostgreSQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d89d3bd",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "def save_data_range(symbol, engine, start_date=None, end_date=None):\n",
    "    data = get_stock_data(symbol, start_date, end_date)\n",
    "    data.to_sql(\"stock_data\", engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "189d32d9",
   "metadata": {
    "lines_to_next_cell": 2
   },
   "source": [
    "Function to save the stock data for the last trading session to a PostgreSQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ced74766",
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_last_trading_session(symbol, engine):\n",
    "    today = pd.Timestamp.today()\n",
    "    data = get_stock_data(symbol, today, today)\n",
    "    data.to_sql(\"stock_data\", engine, if_exists=\"append\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0615d8d",
   "metadata": {},
   "source": [
    "Save data for multiple stock symbols in the specified date range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c6801bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "for symbol in [\"SPY\", \"QQQ\", \"DIA\"]:\n",
    "    save_data_range(\n",
    "        symbol, engine=engine, start_date=\"2020-06-01\", end_date=\"2023-01-01\"\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62545384",
   "metadata": {},
   "source": [
    "Read and display data for the stock symbol \"SPY\" from the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9f6d8a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = pd.read_sql_query(\"SELECT * from stock_data where symbol='SPY'\", engine)\n",
    "display(df_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63bbc32f",
   "metadata": {},
   "source": [
    "Read and display data for \"SPY\" where the volume is greater than 100,000,000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "841121ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2 = pd.read_sql_query(\n",
    "    \"SELECT * from stock_data where symbol='SPY' and volume > 100000000\", engine\n",
    ")\n",
    "display(df_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40a273f4",
   "metadata": {},
   "source": [
    "The best way to build your database is to first download history. You do this once to “backfill” the historic data. Then you schedule the script to run every trading day after the market close to capture that day’s data going forward.\n",
    "\n",
    "There are a lot of references to schedule a Python job on Mac and Windows so I won’t cover it in detail here.\n",
    "\n",
    "Here are two that will work:\n",
    "\n",
    "​Schedule on Mac/Linux​ : https://theautomatic.net/2020/11/18/how-to-schedule-a-python-script-on-a-mac/\n",
    "​Schedule on Windows​ : https://www.jcchouinard.com/python-automation-using-task-scheduler/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3470180",
   "metadata": {},
   "source": [
    "**Jason Strimpel** is the founder of <a href='https://pyquantnews.com/'>PyQuant News</a> and co-founder of <a href='https://www.tradeblotter.io/'>Trade Blotter</a>. His career in algorithmic trading spans 20+ years. He previously traded for a Chicago-based hedge fund, was a risk manager at JPMorgan, and managed production risk technology for an energy derivatives trading firm in London. In Singapore, he served as APAC CIO for an agricultural trading firm and built the data science team for a global metals trading firm. Jason holds degrees in Finance and Economics and a Master's in Quantitative Finance from the Illinois Institute of Technology. His career spans America, Europe, and Asia. He shares his expertise through the <a href='https://pyquantnews.com/subscribe-to-the-pyquant-newsletter/'>PyQuant Newsletter</a>, social media, and has taught over 1,000+ algorithmic trading with Python in his popular course **<a href='https://gettingstartedwithpythonforquantfinance.com/'>Getting Started With Python for Quant Finance</a>**. All code is for educational purposes only. Nothing provided here is financial advise. Use at your own risk."
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
